﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using DomainModel;

namespace Utils
{
    public class DataUtils
    {
        public static List<T> GetList<T>(string sp_name, object param = null)
        {
            var result = SqlUtilities.Query<T>(sp_name, param).ToList();
            if (result == null)
            {
                result = new List<T>();
            }
            return result;
        }

        public static List<T> GetListFromText<T>(string sqlText, object param = null)
        {
            var result = SqlUtilities.Query<T>(sqlText, param, CommandType.Text).ToList();
            if (result == null)
            {
                result = new List<T>();
            }
            return result;
        }

        public static T GetOne<T>(string sp_name, object param = null) where T : new()
        {
            T result = SqlUtilities.Query<T>(sp_name, param).FirstOrDefault();
            if (result == null)
            {
                result = new T();
            }
            return result;
        }

        public static T GetOneFromText<T>(string sqlText, object param = null) where T : new()
        {
            T result = SqlUtilities.Query<T>(sqlText, param, CommandType.Text).FirstOrDefault();
            if (result == null)
            {
                result = new T();
            }
            return result;
        }

        public static int GetCount(string storedProcedureName)
        {
            return SqlUtilities.Query<int>(storedProcedureName).First();
        }

        public static int GetCountFromText(string sqlText)
        {
            return SqlUtilities.Query<int>(sqlText, null, CommandType.Text, null).First();
        }

        public static Login GetLoginByID(int loginID)
        {
            return SqlUtilities.Query<Login>(ConstUtils.SP_GetLoginByID, new { LoginID = loginID}).FirstOrDefault();
        }

        public static Boolean ExistsUserName(string userName)
        { 
            if (string.IsNullOrEmpty(userName)) return false;
            Login login = SqlUtilities.Query<Login>(ConstUtils.SP_GetLoginByUserName, new { UserName = userName }).FirstOrDefault();
            if (login != null && login.LoginID > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// name may be userName or email format
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static Login GetLoginByName(string name)
        {
            Login login = null;
            if (!string.IsNullOrEmpty(name))
            {
                //username is email format
                if (name.Contains("@"))
                {
                    login = SqlUtilities.Query<Login>(ConstUtils.SP_GetLoginByEmail, new { Email = name }).FirstOrDefault();
                }
                //username is username format
                else
                {
                    login = SqlUtilities.Query<Login>(ConstUtils.SP_GetLoginByUserName, new { UserName = name }).FirstOrDefault();
                }
            }
            return login;
        }

        public static string GetPasswordHash(string name)
        { 
            if (string.IsNullOrEmpty(name))
            {
                return string.Empty;
            }
            Login login = GetLoginByName(name);
            if (login != null)
            {
                return login.PasswordHash;
            }
            else
            {
                return string.Empty;
            }
        }

        public static IndLogin GetIndLoginByIndID(int indID)
        {
            var q = SqlUtilities.QueryMultiple<Login, Ind>(ConstUtils.SP_GetLoginAndIndByIndID, new { IndID = indID });
            var login = ((IEnumerable<Login>)q.Dequeue()).FirstOrDefault();
            var ind = ((IEnumerable<Ind>)q.Dequeue()).FirstOrDefault();

            return new IndLogin { Login = login, Ind = ind };
        }

        public static IndLogin GetIndLoginByID(int loginID)
        {
            var q = SqlUtilities.QueryMultiple<Login, Ind>(ConstUtils.SP_GetLoginAndInd, new { LoginID = loginID });
            var login = ((IEnumerable<Login>)q.Dequeue()).FirstOrDefault();
            var ind = ((IEnumerable<Ind>)q.Dequeue()).FirstOrDefault();

            return new IndLogin { Login = login, Ind = ind };
        }

        public static IndLogin GetIndLoginByUserName(string userName)
        {
            var q = SqlUtilities.QueryMultiple<Login, Ind>(ConstUtils.SP_GetLoginAndIndByUserName, new { userName });
            var login = ((IEnumerable<Login>)q.Dequeue()).FirstOrDefault();
            var ind = ((IEnumerable<Ind>)q.Dequeue()).FirstOrDefault();

            return new IndLogin { Login = login, Ind = ind };
        }

        public static CoLogin GetCoLoginByID(int loginID)
        {
            var q = SqlUtilities.QueryMultiple<Login, Co>(ConstUtils.SP_GetLoginAndCo, new { LoginID = loginID });
            var login = ((IEnumerable<Login>)q.Dequeue()).FirstOrDefault();
            var co = ((IEnumerable<Co>)q.Dequeue()).FirstOrDefault();

            return new CoLogin { Login = login, Co = co };
        }

        public static CoJob GetCoJobByJobTokenAndCoID(string jobGUID, int jobID, int coID)
        {
            var q = SqlUtilities.QueryMultiple<Co, Job>(ConstUtils.SP_GetCoJobByJobTokenAndCoID, new { jobGUID, jobID, coID });
            var co = ((IEnumerable<Co>)q.Dequeue()).FirstOrDefault();
            var job = ((IEnumerable<Job>)q.Dequeue()).FirstOrDefault();
            if (co == null) co = new Co();
            if (job == null) job = new Job();
            return new CoJob { Co = co, Job = job };
        }

        public static CoJob GetCoJobByJobID(int jobID)
        {
            var q = SqlUtilities.QueryMultiple<Co, Job>(ConstUtils.SP_GetCoJobByJobID, new { jobID });
            var co = ((IEnumerable<Co>)q.Dequeue()).FirstOrDefault();
            var job = ((IEnumerable<Job>)q.Dequeue()).FirstOrDefault();
            if (co == null) co = new Co();
            if (job == null) job = new Job();
            return new CoJob { Co = co, Job = job };
        }

        public static int GetCoJobCredits(int loginID, int coID)
        {
            Co co = DataUtils.GetOne<Co>(ConstUtils.SP_GetCo, new { loginID, coID });
            if (co != null)
            {
                return co.JobCredits;
            }
            else
            {
                return 0;
            }
        }

        public static void InsertEmailLog(int emailTemplateType, int senderLoginID, int receiverLoginID, EmailMessage message, EmailResult result)
        {
            SqlUtilities.Execute(ConstUtils.SP_InsertEmailMessage, new
            {
                EmailTemplateType = emailTemplateType,
                SenderLoginID = senderLoginID,
                ReceiverLoginID = receiverLoginID,
                From = message.From.Address,
                To = string.Join(";", message.To.Select(x => x.Address).ToList()),
                Bcc = (message.BCC != null && message.BCC.Count > 0) ? string.Join(";", message.BCC.Select(x => x.Address)) : string.Empty,
                Subject = message.Subject,
                Body = message.Body,
                Success = result.Success,
                ErrorMessage = result.ErrorMessage
            }, null, true);
        }

        public static void InsertEmailTemplate(string emailTemplateName, string subject, string body, string bodyParameter)
        {
            SqlUtilities.Execute(ConstUtils.SP_InsertEmailTemplate, new
            {
                EmailTemplateName = emailTemplateName,
                Subject = subject,
                Body = body,
                BodyParameter = bodyParameter
            }, null, true);
        }

        public static void UpdateEmailTemplate(int emailTemplateID, int emailTemplateType, string emailTemplateName, string subject, string body, string bodyParameter)
        {
            SqlUtilities.Execute(ConstUtils.SP_UpdateEmailTemplate, new
                {
                    EmailTemplateID = emailTemplateID,
                    EmailTemplateType = emailTemplateType,
                    EmailTemplateName = emailTemplateName,
                    Subject = subject,
                    Body = body,
                    BodyParameter = bodyParameter
                }, null, true);
        }

        public static void DeleteEmailTemplate(int emailTemplateID)
        {
            SqlUtilities.Execute(ConstUtils.SP_DeleteEmailTemplate, new { EmailTemplateID = emailTemplateID }, null, true);
        }

        public static int InsertShot(int indID, string title, string description, int width, int height, int fileSize, string fileExtension, string orginalFileName)
        {
            int shotID = -1;
            try
            {
                shotID = SqlUtilities.Query<int>(ConstUtils.SP_InsertShot, new { 
                    IndID = indID,
                    Title = title,
                    Description = description,
                    Width = width,
                    Height = height,
                    FileSize = fileSize,
                    FileExtension = fileExtension,
                    OrginalFileName = orginalFileName
                }, null, true).FirstOrDefault();
            }
            catch
            {
            }
            return shotID;
        }

        public static int UpdateAvatar(AvatarType avatarType, int id, string avatarFileExtension)
        {
            try
            {
                SqlUtilities.Execute(ConstUtils.SP_UpdateAvatar, new { ID = id, AvatarType = (int)avatarType, AvatarFileExtension = avatarFileExtension }, null, true);
                return 1;
            }
            catch {
                return -1;
            }
        }

        public static List<Skill> GetSkillListByID(int indID)
        {
            return GetList<Skill>(ConstUtils.SP_GetSkillListByID, new { IndID = indID });
        }

        public static int InsertSkill(int indID, string skillName)
        {
            int skillID = -1;
            try
            {
                skillID = SqlUtilities.Query<int>(ConstUtils.SP_InsertSkill, new
                    {
                        IndID = indID,
                        SkillName = skillName
                    }, null, true).FirstOrDefault();
            }
            catch
            {
            }
            return skillID;
        }

        public static int InsertShotTag(int indID, int shotID, string name)
        {
            int stagID = -1;
            try
            {
                stagID = SqlUtilities.Query<int>(ConstUtils.SP_InsertShotTag, new 
                    { 
                        IndID = indID,
                        ShotID = shotID,
                        Name = name
                    }, null, true).FirstOrDefault();
            }
            catch
            { }
            return stagID;
        }

        public static void UpdateShot(int indID, int shotID, string title, string description)
        {
            SqlUtilities.Execute(ConstUtils.SP_UpdateShot, 
                new { 
                    IndID = indID, 
                    ShotID = shotID,
                    Title = title,
                    Description = description
                },
                null, true
            );
        }

        public static void DeleteShot(int indID, int shotID)
        {
            SqlUtilities.Execute(ConstUtils.SP_DeleteShot,
                new { 
                    IndID = indID,
                    ShotID = shotID
                },
                null, true
            );
        }

        public static void DeleteAccount(int loginID, int accountID, string accountType)
        {
            SqlUtilities.Execute(ConstUtils.SP_DeleteAccount,
                new
                {
                    LoginID = loginID,
                    AccountID = accountID,
                    AccountType = accountType
                },
                null, true
            );
        }

        //insert co first, and then get new coid, then insert into job
        public static Job InsertCoAndInsertJob(Co co, Job job)
        {
            job.Description = job.Description.ToRichHtml();
            co.Description = co.Description.ToRichHtml();
            //return jobToken
            Job result = SqlUtilities.Query<Job>(ConstUtils.SP_InsertCoAndInsertJob, 
                new {
                    CoName = co.Name, 
                    CoTypeID = co.CoTypeID,
                    CoUrl = co.CoUrl,
                    CoDescription = co.Description,
                    CoLocation = co.Location,

                    JobGUID = Guid.NewGuid().ToString(), //new guid
                    Title = job.Title,
                    Description = job.Description,
                    JobTypeID = job.JobTypeID,
                    JobExpID = job.JobExpID,
                    Location = job.Location,
                    AllowTelecommute = job.AllowTelecommute,
                    RelocationOffered = job.RelocationOffered,
                    OnlinePortfolioRequired = job.OnlinePortfolioRequired,
                    OnlinePDFResumeRequired = job.OnlinePDFResumeRequired,
                    Compensation = job.Compensation,
                    Perk = job.Perk,
                    ApplyViaEmail = job.ApplyViaEmail,
                    ApplyEmail = job.ApplyEmail,
                    ApplyUrl = job.ApplyUrl,
                    JobKeywords = job.JobKeywords
                }, null, true).FirstOrDefault();
            if (result == null)
            {
                result = new Job();
            }
            return result;
        }


        //update job first, if success, and then update co where coid is in job table
        public static Job UpdateJobAndUpdateCo(Co co, Job job, int jobID, string jobGUID, int coID)
        {
            job.Description = job.Description.ToRichHtml();
            co.Description = co.Description.ToRichHtml();
            //return jobToken
           var result = SqlUtilities.Query<Job>(ConstUtils.SP_UpdateJobAndUpdateCo,
                new
                {
                    CoID = coID,
                    CoName = co.Name,
                    CoTypeID = co.CoTypeID,
                    CoUrl = co.CoUrl,
                    CoDescription = co.Description,
                    CoLocation = co.Location,

                    JobID = jobID,
                    JobGUID = jobGUID, //identifier
                    Title = job.Title,
                    Description = job.Description,
                    JobTypeID = job.JobTypeID,
                    JobExpID = job.JobExpID,
                    Location = job.Location,
                    AllowTelecommute = job.AllowTelecommute,
                    RelocationOffered = job.RelocationOffered,
                    OnlinePortfolioRequired = job.OnlinePortfolioRequired,
                    OnlinePDFResumeRequired = job.OnlinePDFResumeRequired,
                    Compensation = job.Compensation,
                    Perk = job.Perk,
                    ApplyViaEmail = job.ApplyViaEmail,
                    ApplyEmail = job.ApplyEmail,
                    ApplyUrl = job.ApplyUrl,
                    JobKeywords = job.JobKeywords
                }, null, true).FirstOrDefault();
           if (result == null)
           {
               result = new Job();
           }
           return result;
        }

        //update co first, and then insert job
        public static Job UpdateCoAndInsertJob(Co co, Job job, int coID)
        {
            job.Description = job.Description.ToRichHtml();
            co.Description = co.Description.ToRichHtml();
            Job result = SqlUtilities.Query<Job>(ConstUtils.SP_UpdateCoAndInsertJob,
                new
                {
                    CoID = coID,
                    CoName = co.Name,
                    CoTypeID = co.CoTypeID,
                    CoUrl = co.CoUrl,
                    CoDescription = co.Description,
                    CoLocation = co.Location,

                    JobGUID = Guid.NewGuid().ToString(), //new guid
                    Title = job.Title,
                    Description = job.Description,
                    JobTypeID = job.JobTypeID,
                    JobExpID = job.JobExpID,
                    Location = job.Location,
                    AllowTelecommute = job.AllowTelecommute,
                    RelocationOffered = job.RelocationOffered,
                    OnlinePortfolioRequired = job.OnlinePortfolioRequired,
                    OnlinePDFResumeRequired = job.OnlinePDFResumeRequired,
                    Compensation = job.Compensation,
                    Perk = job.Perk,
                    ApplyViaEmail = job.ApplyViaEmail,
                    ApplyEmail = job.ApplyEmail,
                    ApplyUrl = job.ApplyUrl,
                    JobKeywords = job.JobKeywords
                }, null, true).FirstOrDefault();
            if (result == null)
            {
                result = new Job();
            }
            return result;
        }

        //update job
        public static void UpdateJobForEdit(Job job, int coID)
        {
            job.Description = job.Description.ToRichHtml();
            SqlUtilities.Execute(ConstUtils.SP_UpdateJobForEdit,
                 new
                 {
                     CoID = coID,
                     JobID = job.JobID,
                     Title = job.Title,
                     Description = job.Description,
                     JobTypeID = job.JobTypeID,
                     JobExpID = job.JobExpID,
                     Location = job.Location,
                     AllowTelecommute = job.AllowTelecommute,
                     RelocationOffered = job.RelocationOffered,
                     OnlinePortfolioRequired = job.OnlinePortfolioRequired,
                     OnlinePDFResumeRequired = job.OnlinePDFResumeRequired,
                     Compensation = job.Compensation,
                     Perk = job.Perk,
                     ApplyViaEmail = job.ApplyViaEmail,
                     ApplyEmail = job.ApplyEmail,
                     ApplyUrl = job.ApplyUrl,
                     JobKeywords = job.JobKeywords
                 }, null, true);
        }

        public static List<IndLogin> GetSavedTalentAndShotList(int loginID, int page, int numPerPage)
        {
            var q = SqlUtilities.QueryMultiple<Login, Ind, Shot>(ConstUtils.SP_GetSavedTalentAndShotList, new { loginID, page, numPerPage });
            var loginList = ((IEnumerable<Login>)q.Dequeue()).ToList();
            var indList = ((IEnumerable<Ind>)q.Dequeue()).ToList();
            var shotList = ((IEnumerable<Shot>)q.Dequeue()).ToList();
            
            List<IndLogin> indLoginList = new List<IndLogin>();
            foreach (var ind in indList)
            {
                var indLogin = new IndLogin();
                var login = loginList.Find(x=>x.LoginID == ind.LoginID);
                ind.ShotList = shotList.FindAll(x => x.IndID == ind.IndID);
                indLogin.Login = login;
                indLogin.Ind = ind;
                indLoginList.Add(indLogin);
            }

            return indLoginList;
        }

        public static List<CoJob> GetSearchJobs(string tag)
        {
            return SqlUtilities.Query<Co, Job, CoJob>(ConstUtils.SP_SearchJobs,
                (co, job) =>
                {
                    CoJob cojob = new CoJob();
                    cojob.Co = co;
                    cojob.Job = job;
                    return cojob;
                }, new { tag }, "splitid", null, null).ToList();
        }
    }
}